using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;

namespace MvcExtExample.ReportViews
{
    public partial class OilFieldMonthRecordReport : System.Web.UI.Page
    {
        List<string> workZoneList = new List<string>();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["LogedUser"] == null)
                Response.Redirect("http://localhost:8132/home/index");
            if (!IsPostBack)
            {
                //获取所有工作区的名称
                workZoneList = new List<string>();
                workZoneList = GetWorkZoneData();
                this.productdate_box.Value = "2014-09";
                ReportViewer1.LocalReport.Refresh();
                DataTable dt = GetData();
                ReportDataSource rds = new ReportDataSource("OilFieldMonthRecordDataSet", dt);
                this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("..\\Reports\\OilFieldMonthRecordReport.rdlc");
                ReportViewer1.KeepSessionAlive = true;//解决ASP.NET session has expired or could not be found问题
                ReportViewer1.LocalReport.DataSources.Clear();
                ReportViewer1.LocalReport.DataSources.Add(rds);
                ReportViewer1.LocalReport.Refresh();
            }
        }

        //获取所有工作区的名称
        private List<string> GetWorkZoneData()
        {
            List<string> WorkZone = new List<string>();
            string strSqlStr = "SELECT [Dep_Name] FROM [dbo].[Tb_Department] WHERE [ParentDep_ID]='ALL'";
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            DataSet DSet = cs.GetDataSet(strSqlStr);
            if (DSet != null)
            {
                DataTable DTable = DSet.Tables[0];
                for (int i = 0; i < DTable.Rows.Count; i++)
                {
                    DataRowView rowview = DSet.Tables[0].DefaultView[i];
                    WorkZone.Add(rowview["Dep_Name"].ToString());
                }
            }
            return WorkZone;
        }

        //从数据库读取报表所需数据
        private DataTable GetData()
        {
            List<decimal> CYJDailyOil = new List<decimal>(), CYJDailyGas = new List<decimal>(), CYJDailyFluid = new List<decimal>(), CYJDailyContainWater = new List<decimal>(), ZSJDaily_Schedule = new List<decimal>(), ZSJDaily_Actual = new List<decimal>(), CQJDailyGas = new List<decimal>(), CQJDailyWater = new List<decimal>(), CQJDailyOil = new List<decimal>();
            List<decimal> TotalWells = new List<decimal>(), CYJOpenWells = new List<decimal>(), ZSJOpenWells = new List<decimal>(), CQJOpenWells = new List<decimal>();
            List<string> Date = new List<string>();
            string productdate = this.productdate_box.Value;
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            //获取这一个月的每天日期
            string month = productdate.Substring(productdate.Length - 2, 2);
            string year = productdate.Substring(0, 4);
            string strSqlGetDateList = "SELECT DISTINCT [Date] FROM [dbo].[Tb_Oil_Well] WHERE MONTH([Date])=" + month + " AND YEAR([Date])=" + year;
            DataSet DateDateSet = cs.GetDataSet(strSqlGetDateList);
            if (DateDateSet != null)
            {
                DataTable DTable = DateDateSet.Tables[0];
                for (int i = 0; i < DTable.Rows.Count; i++)
                {
                    DataRowView rowview = DateDateSet.Tables[0].DefaultView[i];
                    Date.Add(rowview["Date"].ToString());
                }
            }
            //获取每个作业区这一个月内每天的井总数
            for (int i = 1; i <= workZoneList.Count; i++)
            {
                foreach (string item in Date)
                {
                    string strSqlGetDailyOilWell = "SELECT COUNT(*)FROM [dbo].[Tb_Oil_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    string strSqlGetDailyWaterWell = "SELECT COUNT(*)FROM [dbo].[Tb_Water_Injection_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    string strSqlGetDailyTb_Gas_Well = "SELECT COUNT(*)FROM [dbo].[Tb_Gas_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    decimal DailyOilWellNum = cs.ReturnSQL(strSqlGetDailyOilWell);
                    decimal DailyWaterWellNum = cs.ReturnSQL(strSqlGetDailyWaterWell);
                    decimal DailyGasWellNum = cs.ReturnSQL(strSqlGetDailyTb_Gas_Well);
                    CYJOpenWells.Add(DailyOilWellNum);
                    ZSJOpenWells.Add(DailyWaterWellNum);
                    CQJOpenWells.Add(DailyGasWellNum);
                    TotalWells.Add(DailyOilWellNum + DailyWaterWellNum + DailyGasWellNum);
                }
                #region
                //获取该采油厂下的所有作业区,load中完成
                //获取每个作业区内这一个月的平均日开井数
                foreach (string item in Date)
                {
                    string strSqlGetDailyOilWell = "SELECT COUNT(*)FROM [dbo].[Tb_Oil_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    string strSqlGetDailyWaterWell = "SELECT COUNT(*)FROM [dbo].[Tb_Water_Injection_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    string strSqlGetDailyTb_Gas_Well = "SELECT COUNT(*)FROM [dbo].[Tb_Gas_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    int DailyOilWellNum = cs.ReturnSQL(strSqlGetDailyOilWell);
                    int DailyWaterWellNum = cs.ReturnSQL(strSqlGetDailyWaterWell);
                    int DailyGasWellNum = cs.ReturnSQL(strSqlGetDailyTb_Gas_Well);
                    CYJOpenWells.Add(DailyOilWellNum / Date.Count);
                    ZSJOpenWells.Add(DailyWaterWellNum / Date.Count);
                    CQJOpenWells.Add(DailyGasWellNum);
                    TotalWells.Add((DailyOilWellNum + DailyWaterWellNum + DailyGasWellNum) / Date.Count);
                }
                //获取每个作业区内这一个月的油井相关指标
                foreach (string item in Date)
                {
                    //获取这一个月内每天的日产液总量
                    string strSqlGetDailyFluid = "SELECT SUM([DailyOil])FROM [dbo].[Tb_Oil_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    decimal DailyTotalFluid = cs.ReturnDecimalSQL(strSqlGetDailyFluid);
                    CYJDailyFluid.Add(DailyTotalFluid);
                    //获取这一个月内每天的日产油总量
                    string strSqlGetDailyOil = "SELECT SUM([DailyOil])FROM [dbo].[Tb_Oil_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    decimal DailyTotalOil = cs.ReturnDecimalSQL(strSqlGetDailyOil);
                    CYJDailyOil.Add(DailyTotalOil);
                    //获取这一个月内每天的日产气总量
                    string strSqlGetDailyGas = "SELECT SUM(DailyFluid)FROM [dbo].[Tb_Oil_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],6,1)='" + i + "'";
                    decimal DailyTotalGas = cs.ReturnDecimalSQL(strSqlGetDailyGas);
                    CYJDailyGas.Add(DailyTotalFluid);
                }
                //获取这一个月内每天的水井相关指标
                foreach (string item in Date)
                {
                    //获取这一个月内每天的标注
                    string strSqlGetDaily_Schedule = "SELECT SUM(Daily_Schedule)FROM [dbo].Tb_Water_Injection_Well WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],5,1)='" + i + "'";
                    decimal Daily_Schedule = cs.ReturnDecimalSQL(strSqlGetDaily_Schedule);
                    ZSJDaily_Schedule.Add(Daily_Schedule);
                    //获取这一个月内每天的实注
                    string strSqlGetDaily_Actual = "SELECT SUM(Daily_Actual)FROM [dbo].[Tb_Water_Injection_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],5,1)='" + i + "'";
                    decimal Daily_Actual = cs.ReturnDecimalSQL(strSqlGetDaily_Actual);
                    ZSJDaily_Actual.Add(Daily_Actual);
                }
                //获取这一个月内每天的气井相关指标
                foreach (string item in Date)
                {
                    //获取这一个月内每天的日产气
                    string strSqlGetDaily_Gas = "SELECT SUM(DailyGas)FROM [dbo].Tb_Gas_Well WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],5,1)='" + i + "'";
                    decimal Daily_Gas = cs.ReturnDecimalSQL(strSqlGetDaily_Gas);
                    CQJDailyGas.Add(Daily_Gas);
                    //获取这一个月内每天的日产油
                    string strSqlGetDailyOil = "SELECT SUM(DailyOil)FROM [dbo].[Tb_Gas_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],5,1)='" + i + "'";
                    decimal DailyOil = cs.ReturnDecimalSQL(strSqlGetDailyOil);
                    CQJDailyOil.Add(DailyOil);
                    //获取这一个月内每天的日产水
                    string strSqlGetDailyWaterCQJ = "SELECT SUM(DailyWater)FROM [dbo].[Tb_Gas_Well]WHERE [Date] = '" + item + "' AND SUBSTRING([Well_ID],5,1)='" + i + "'";
                    decimal DailyWaterCQJ = cs.ReturnDecimalSQL(strSqlGetDailyWaterCQJ);
                    CQJDailyWater.Add(DailyWaterCQJ);
                }
            }
            #endregion
            DataTable dt = new DataTable("TB_OilFieldReportMonth");//这个名字是无关紧要的，xsd的DataTable也可以随意命名
            dt.Columns.Add(new DataColumn("WorkZone", typeof(string)));
            dt.Columns.Add(new DataColumn("TotalWells", typeof(string)));
            dt.Columns.Add(new DataColumn("CYJOpenWells", typeof(string)));
            dt.Columns.Add(new DataColumn("CYJTotalFluid", typeof(string)));
            dt.Columns.Add(new DataColumn("CYJTotalOil", typeof(string)));
            dt.Columns.Add(new DataColumn("CYJTotalGas", typeof(string)));
            dt.Columns.Add(new DataColumn("ZSJOpenWells", typeof(string)));
            dt.Columns.Add(new DataColumn("ZSJTotalSchedule", typeof(string)));
            dt.Columns.Add(new DataColumn("ZSJTotalActual", typeof(string)));
            dt.Columns.Add(new DataColumn("CQJOpenWells", typeof(string)));
            dt.Columns.Add(new DataColumn("CQJTotalGas", typeof(string)));
            dt.Columns.Add(new DataColumn("CQJTotalwater", typeof(string)));
            dt.Columns.Add(new DataColumn("CQJTotalOil", typeof(string)));
            if (Date.Count <= 0)
                return null;
            for (int i = 0; workZoneList.Count > 0 && i < workZoneList.Count; i++)
            {
                DataRow row = dt.NewRow();
                row["WorkZone"] = workZoneList[i];
                row["TotalWells"] = TotalWells[i];
                row["CYJOpenWells"] = CYJOpenWells[i];
                row["CYJTotalOil"] = CYJDailyOil[i];
                row["CYJTotalFluid"] = CYJDailyFluid[i];
                row["CYJTotalGas"] = CYJDailyGas[i];
                row["ZSJOpenWells"] = ZSJOpenWells[i];
                row["ZSJTotalSchedule"] = ZSJDaily_Schedule[i];
                row["ZSJTotalActual"] = ZSJDaily_Actual[i];
                row["CQJOpenWells"] = CQJOpenWells[i];
                row["CQJTotalGas"] = CQJDailyGas[i];
                row["CQJTotalwater"] = CQJDailyWater[i];
                row["CQJTotalOil"] = CQJDailyOil[i];
                dt.Rows.Add(row);
            }
            return dt;
        }

        protected void queryBtn_Click(object sender, EventArgs e)
        {
            workZoneList = new List<string>();
            workZoneList = GetWorkZoneData();
            ReportViewer1.LocalReport.Refresh();
            DataTable dt = GetData();
            ReportDataSource rds = new ReportDataSource("OilFieldMonthRecordDataSet", dt);
            this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("..\\Reports\\OilFieldMonthRecordReport.rdlc");
            ReportViewer1.KeepSessionAlive = true;//解决ASP.NET session has expired or could not be found问题
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(rds);
            ReportViewer1.LocalReport.Refresh();
        }

    }
}